package com.kyivmohyla.kma2.dao;

import com.kyivmohyla.kma2.dao.mapper.CustomerRowMapper;
import com.kyivmohyla.kma2.model.Customer;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import java.util.List;

public class CustomerDaoJdbc extends JdbcDaoSupport implements CustomerDao {

    @Override
    public Customer getById(int id) throws DataAccessException {
        return getJdbcTemplate()
                .queryForObject(
                        "SELECT customer.customer_id, customer.first_name, customer.last_name, customer.address, customer.phone, customer.email FROM customer WHERE customer.customer_id = ?",
                        new Object[]{id}, new CustomerRowMapper());
    }

    @Override
    public List<Customer> getAll() throws DataAccessException {
        return getJdbcTemplate()
                .query("SELECT customer.customer_id, customer.first_name, customer.last_name, customer.address, customer.phone, customer.email FROM customer ORDER BY customer.customer_id",
                        new CustomerRowMapper());
    }

    @Override
    public void create(Customer customer) throws DataAccessException {
        getJdbcTemplate()
                .update("INSERT INTO customer (customer.customer_id, customer.first_name, customer.last_name, customer.address, customer.phone, customer.email) VALUES (customer_seq.NEXTVAL, ?, ?, ?, ?, ?)",
                        customer.getFirstName(),
                        customer.getLastName(),
                        customer.getAddress(), customer.getPhone(),
                        customer.getEmail());
    }

    @Override
    public void update(Customer customer) throws DataAccessException {
        getJdbcTemplate()
                .update("UPDATE customer SET customer.first_name = ?, customer.last_name = ?, customer.address = ?, customer.phone = ?, customer.email = ? WHERE customer.customer_id = ?",
                        customer.getFirstName(),
                        customer.getLastName(),
                        customer.getAddress(), customer.getPhone(),
                        customer.getEmail(),
                        customer.getCustomerId());
    }

    @Override
    public void delete(Customer customer) throws DataAccessException {
        getJdbcTemplate().update(
                "DELETE FROM customer WHERE customer.customer_id = ?",
                customer.getCustomerId());
    }

    @Override
    public Customer getCustomerWithMaxId() throws DataAccessException {
        return getJdbcTemplate().queryForObject("SELECT customer.customer_id, customer.first_name, customer.last_name, customer.address, customer.phone, customer.email FROM customer WHERE customer.customer_id = (Select MAX(customer_id) FROM Customer)", new CustomerRowMapper());
    }
}
